package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.cookbook.vo.IngredientsType;
import com.wanmait.cookbook.vo.IngredientsTypeSecond;
import com.wanmait.cookbook.vo.Pager;
import com.wanmait.cookbook.vo.RecipeType;
import com.wanmait.cookbook.vo.RecipeTypeSecond;
import com.wanmait.demo.util.DBHelper;

/*
 * 食谱分类二级表DAO
 */
public class RecipeTypeSecondDAO {
	private RecipeTypeSecondDAO() {}
	private static RecipeTypeSecondDAO recipeTypeSecondDAO = new RecipeTypeSecondDAO();
	public static RecipeTypeSecondDAO getRecipeTypeSecondDAO() {
		return recipeTypeSecondDAO;
	}
	 // 通过二级id 查找同类的二级食谱  草稿箱 用
	  public List<RecipeTypeSecond> findRecipeTypeSecondsBySecondId(Integer secondId) {
	    List<RecipeTypeSecond> recipeTypeSeconds = new ArrayList<RecipeTypeSecond>();
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    String sql = "SELECT * FROM recipe_type_second WHERE recipe_type_id=(SELECT recipe_type_id FROM recipe_type_second WHERE id=?) AND ENABLE=1";
	    try {
	      conn = DBHelper.getHelper().getConn();
	      ps = conn.prepareStatement(sql);
	      ps.setInt(1, secondId);
	      rs = ps.executeQuery();
	      RecipeTypeSecond rTypeSecond = null;
	      while (rs.next()) {
	        rTypeSecond = new RecipeTypeSecond();
	        rTypeSecond.setId(rs.getInt("id"));
	        rTypeSecond.setTitle(rs.getString("title"));
	        RecipeType recipeType = new RecipeType();
	        recipeType.setId(rs.getInt("recipe_type_id"));
	        rTypeSecond.setRecipeType(recipeType);
	        rTypeSecond.setEnable(rs.getBoolean("enable"));
	        recipeTypeSeconds.add(rTypeSecond);
	      }
	    } catch (SQLException e) {
	      // TODO Auto-generated catch block
	      e.printStackTrace();
	    }
	    finally {
	      DBHelper.getHelper().closeConn(rs, ps, conn);
	    }
	    
	    return recipeTypeSeconds;
	  }

	public List<RecipeTypeSecond> findByRecipeTypeSecond()
	{
		List<RecipeTypeSecond> recipeTypeSeconds=new ArrayList<RecipeTypeSecond>();
		RecipeType recipeType;
		RecipeTypeSecond recipeTypeSecond;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			StringBuilder sb=new StringBuilder();
			sb.append(" SELECT recipe_type_second.id,recipe_type_second.title,recipe_type.title type_title, ");
			sb.append(" recipe_type.id type_id ");
			sb.append(" FROM recipe_type,recipe_type_second ");
			sb.append(" WHERE recipe_type_second.recipe_type_id=recipe_type.id  ");
			sb.append(" AND recipe_type_second.`enable`=1  ");
			sb.append(" AND recipe_type.`enable`=1" );
			String sql=sb.toString();
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			
			rs=ps.executeQuery();
			while(rs.next()) {
				recipeType=new RecipeType();
				recipeType.setId(rs.getInt("type_id"));
				recipeType.setTitle(rs.getString("type_title"));
				
				recipeTypeSecond=new RecipeTypeSecond();
				recipeTypeSecond.setId(rs.getInt("id"));
				recipeTypeSecond.setTitle(rs.getString("title"));
				recipeTypeSecond.setRecipeType(recipeType);
				recipeTypeSecond.setEnable(true);
				recipeTypeSeconds.add(recipeTypeSecond);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally 
		{
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
			return recipeTypeSeconds;
		
	}
	
	//��ö���ʳ�׷���������
		public int getCookbookSecondTypeRows() {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;

			try {
		
				String sql="SELECT count(*) c FROM recipe_type_second WHERE ENABLE=1 ";
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				rs.next();
				int rows = rs.getInt("c");
				return rows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return 0;
		}
	
		
		
		//��ö���ʳ�׷���  ��ҳ
		public List<RecipeTypeSecond> getCookbookSecondType(Pager pager){
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			List<RecipeTypeSecond> recipeTypeSeconds=new ArrayList<RecipeTypeSecond>();
			RecipeTypeSecond recipeTypeSecond=null;
			RecipeType recipeType=null;
			try {
				String sql="SELECT * FROM recipe_type_second,recipe_type "
						+ " WHERE recipe_type.ENABLE=1 "
						+ " AND recipe_type_second.ENABLE=1 "
						+ " AND recipe_type_second.recipe_type_id=recipe_type.id "
						+ " limit ?,?";
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1, pager.getStart());
				ps.setInt(2, pager.getPageSize());
				rs=ps.executeQuery();
				while(rs.next()) {
					recipeType = new RecipeType();
					recipeType.setId(rs.getInt("recipe_type.id"));
					recipeType.setTitle(rs.getString("recipe_type.title"));
					recipeTypeSecond=new RecipeTypeSecond();
					recipeTypeSecond.setId(rs.getInt("recipe_type_second.id"));
					recipeTypeSecond.setTitle(rs.getString("recipe_type_second.title"));
					recipeTypeSecond.setEnable(rs.getBoolean("recipe_type_second.enable"));
					recipeTypeSecond.setRecipeType(recipeType);
					recipeTypeSeconds.add(recipeTypeSecond);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally 
			{
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}	
			return recipeTypeSeconds;
		}
		
		
		// ͨ��IDɾ��ʳ�׶�������
		public void deleteCookbookTypeSecondById(int id) {
			String sql = "update recipe_type_second set enable=0 where id=?";
			DBHelper.getHelper().executeUpdate(sql, id);
		}

		// ͨ��ʳ�׶�������IDsɾ�������������
		public void deleteCookbookTypeSecondsByIds(String ids) {
			String sql = "update recipe_type_second set enable=0 where id in(" + ids + ")";
			DBHelper.getHelper().executeUpdate(sql);
		}
		
		//��Ӷ�������
		public void addCookbookTypeSeconds(RecipeTypeSecond recipeTypeSecond) {
			String sql="insert into recipe_type_second(title,recipe_type_id) values(?,?)";
			DBHelper.getHelper().executeUpdate(sql,recipeTypeSecond.getTitle(),recipeTypeSecond.getRecipeType().getId());
		}
		
		
		//���Ҷ������͸��ݶ�������id
		public RecipeTypeSecond findCookbookTypeSecondByid(int id) {
			RecipeTypeSecond recipeTypeSecond = null;
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql="SELECT * from recipe_type_second,recipe_type "
					+ " WHERE recipe_type_second.id=? "
					+ " AND recipe_type_second.recipe_type_id=recipe_type.id";
			
			try {
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs=ps.executeQuery();
				
				if(rs.next()) {
					recipeTypeSecond = new RecipeTypeSecond();
					recipeTypeSecond.setId(id);
					recipeTypeSecond.setTitle(rs.getString("recipe_type_second.title"));
					RecipeType recipeType = new RecipeType();
					recipeType.setId(rs.getInt("recipe_type.id"));
					recipeType.setTitle(rs.getString("recipe_type.title"));
					recipeType.setEnable(rs.getBoolean("recipe_type.enable"));
					recipeTypeSecond.setRecipeType(recipeType);
					recipeTypeSecond.setEnable(rs.getBoolean("recipe_type_second.enable"));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return recipeTypeSecond;
		}
		
		
		//�޸Ķ���������ͨ��id
		public void upadteCookbookTypeSecondById(RecipeTypeSecond recipeTypeSecond) {
			String sql="UPDATE recipe_type_second SET title=?,recipe_type_id=? WHERE id=?";
			DBHelper.getHelper().executeUpdate(sql,recipeTypeSecond.getTitle(),recipeTypeSecond.getRecipeType().getId(),recipeTypeSecond.getId());
		}
		
		
		// ��ö���ʳ�׷���������ͨ�������ؼ���
		public int getCookbookSecondTypeRowsByInput(String val) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;

			try {

				String sql = "SELECT count(*) c FROM recipe_type_second WHERE ENABLE=1 And title like ?";
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+val+"%");
				rs = ps.executeQuery();
				rs.next();
				int rows = rs.getInt("c");
				return rows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return 0;
		}
		
		// ��ö���ʳ�׷��� ��ҳ
		public List<RecipeTypeSecond> getCookbookSecondTypeByInput(Pager pager,String val) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			List<RecipeTypeSecond> recipeTypeSeconds = new ArrayList<RecipeTypeSecond>();
			RecipeTypeSecond recipeTypeSecond = null;
			RecipeType recipeType = null;
			try {
				String sql = "SELECT * FROM recipe_type_second,recipe_type " + " WHERE recipe_type.ENABLE=1 "
						+ " AND recipe_type_second.ENABLE=1 " 
						+ " AND recipe_type_second.recipe_type_id=recipe_type.id and recipe_type_second.title like ?"
						+ " limit ?,?";
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+val+"%");
				ps.setInt(2, pager.getStart());
				ps.setInt(3, pager.getPageSize());
				rs = ps.executeQuery();
				while (rs.next()) {
					recipeType = new RecipeType();
					recipeType.setId(rs.getInt("recipe_type.id"));
					recipeType.setTitle(rs.getString("recipe_type.title"));
					recipeTypeSecond = new RecipeTypeSecond();
					recipeTypeSecond.setId(rs.getInt("recipe_type_second.id"));
					recipeTypeSecond.setTitle(rs.getString("recipe_type_second.title"));
					recipeTypeSecond.setEnable(rs.getBoolean("recipe_type_second.enable"));
					recipeTypeSecond.setRecipeType(recipeType);
					recipeTypeSeconds.add(recipeTypeSecond);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return recipeTypeSeconds;
		}
}
